<?php
require_once 'lib/BaseModel.php';
class Network extends Tamtay_Table_Abstract
{
    protected $_name = 'mytt_network';
    protected $_primary = 'nwid';
                                                     
    public function getNetworks($type = '', $countryId = 0, $cityId = 0, $name = '')
    {
        $db = $this->getSlaveDB();
        $where = '';
        if ($type != ''){
            $where = 'n.ntid = ' . $type;    
        }                                        
        if ($type == NETWORK_TYPE_LOCATION) {
            $where .= ' AND n.cityID != 0';
        }
        if ($countryId != NULL AND $countryId != 0) {
            $where .= $db->quoteInto(' AND na.nationalId = ?', $countryId);
        }    
        if ($cityId != NULL AND $cityId != 0) {
            $where .= $db->quoteInto(' AND c.cid = ?', $cityId);
        }
        if ($name != NULL) {
            $where .= " AND n.name LIKE '%$name%'";
        }
        if ($where != ''){
            $where = 'WHERE ' . $where;
        }
        $sql = 'SELECT n.*, na.nationalName, c.cid, c.name AS cityName FROM mytt_network n LEFT JOIN mytt_national na ON n.nationalID = na.nationalID LEFT JOIN mytt_cities c ON n.cityID = c.cid ' . $where . ' ORDER BY n.name';
        return $db->fetchAll($sql);
    }
    
    public function getLastUpdatedNetworks()
    {
        $db = $this->getSlaveDB();                                                   
        $sql = 'SELECT * FROM mytt_network n INNER JOIN mytt_network_statistics s ON n.nwid = s.nwid WHERE n.status = ' . NETWORK_PUBLISHED . ' ORDER BY n.changed DESC LIMIT ' . NETWORK_LAST_UPDATED_NETWORKS;
        return $db->fetchAll($sql);
    }
    
    public function getLargestMemberNetworks()
    {
        $db = $this->getSlaveDB();
        $sql = 'SELECT s.nwid FROM mytt_network_statistics s INNER JOIN mytt_network n ON s.nwid = n.nwid WHERE n.status = ' . NETWORK_PUBLISHED . ' ORDER BY s.num_member DESC LIMIT ' . NETWORK_LARGEST_MEMBER_NETWORKS;
        $nids = $db->fetchAll($sql); 
        $in = '';
        for ($i = 0; $i < count($nids); $i++) {
            if ($in != '') {
                $in = $in . ', ';
            }
            $in = $in . (int)$nids[$i]->nwid;
        }
        $in = '(' . $in . ')';    
        
        $sql = 'SELECT * FROM mytt_network n INNER JOIN mytt_network_statistics s ON n.nwid = s.nwid WHERE n.nwid IN ' . $in . 'ORDER BY s.num_member DESC';
        return $db->fetchAll($sql);
    }
    
    public function getNetwork($nid)
    {
        $db = $this->getSlaveDB();
        $sql = 'SELECT * FROM mytt_network n INNER JOIN mytt_network_statistics s ON n.nwid = s.nwid WHERE n.status = ' . NETWORK_PUBLISHED . ' AND n.nwid = ?';
        return $db->fetchRow($sql, $nid);
    }
    
    public function getSchoolsByCity($cid)
    {
        $db = $this->getSlaveDB();
        $sql = 'SELECT * FROM mytt_network WHERE ntid = ' . NETWORK_TYPE_SCHOOL . ' AND cityID = ' . $cid;
        return $db->fetchAll($sql);
    }
    
    public function joinNetwork($uid, $nid)
    {
        if ($this->alreadyJoined($uid, $nid)) {
            return FALSE;
        }
        $db = $this->getMasterDB();
        $sql = 'INSERT INTO mytt_network_user (uid, nwid, datejoin) VALUES (?,?,?)';
        $db->query($sql, array($uid, $nid, time()));
        $data = array('changed' => time());
        $where = $this->getDefaultAdapter()->quoteInto('nwid = ?', $nid);
        $this->update($data, $where);
        $sql = 'UPDATE mytt_network_statistics SET num_member = num_member + 1 WHERE nwid = ?';
        $db->query($sql, $nid);
    }
    
    public function quitNetwork($uid, $nid)
    {         
        $db = $this->getMasterDB();   
        $sql = 'DELETE FROM mytt_network_user WHERE uid = ? AND nwid = ?';
        $db->query($sql, array($uid, $nid));
        $data = array('changed' => time());
        $where = $this->getDefaultAdapter()->quoteInto('nwid = ?', $nid);
        $this->update($data, $where);
        $network = $this->getNetwork($nid);
        if ($network) {
            if ($network->num_member > 0) {
                $sql = 'UPDATE mytt_network_statistics SET num_member = num_member - 1 WHERE nwid = ?';        
                $db->query($sql, $nid);
            }
        }                              
    }
    
    public function alreadyJoined($uid, $nid)
    {
        $db = $this->getSlaveDB();
        $sql = 'SELECT * FROM mytt_network_user WHERE uid = ? AND nwid = ?';
        $record = $db->fetchRow($sql, array($uid, $nid));
        if ( ! $record) {
            return FALSE;
        }
        return TRUE;
    } 
    
    public function getUserNetworks($uid, $limit = NULL)
    {
        $db = $this->getSlaveDB();
        $sql = 'SELECT * FROM mytt_network n INNER JOIN mytt_network_user u ON n.nwid = u.nwid AND u.uid = ? INNER JOIN mytt_network_statistics s ON n.nwid = s.nwid WHERE n.status = ' . NETWORK_PUBLISHED . ' ORDER BY n.changed DESC';
        if ($limit != NULL){
            $sql = $sql . ' LIMIT ' . $limit;
        }
        return $db->fetchAll($sql, $uid);
    }   
    
    public function countUserNetworks($uid)
    {
        $db = $this->getSlaveDB();
        $sql = 'SELECT COUNT(*) FROM mytt_network n INNER JOIN mytt_network_user u ON n.nwid = u.nwid AND u.uid = ? INNER JOIN mytt_network_statistics s ON n.nwid = s.nwid WHERE n.status = ' . NETWORK_PUBLISHED . ' ORDER BY n.changed DESC';
        return $db->fetchOne($sql, $uid); 
    }     
    
    public function getMembers($nid, $page = 0)
    {                                                     
        $data = array();
        $db = $this->getSlaveDB();
        $limit = '';
        if ($page != 0){
            $offset = ($page - 1) * NETWORK_MEMBERS;     
            $limit = ' LIMIT ' . NETWORK_MEMBERS . ' OFFSET ' . $offset;       
        }                         
        $sql = 'SELECT u.* FROM users u INNER JOIN mytt_network_user nu ON u.uid = nu.uid INNER JOIN mytt_network n ON nu.nwid = n.nwid WHERE n.nwid = ? AND n.status = ' . NETWORK_PUBLISHED . ' ORDER BY nu.dateJoin DESC' . $limit; 
        return $db->fetchAll($sql, $nid);        
    }
    
    public function getNetworkUID($nid)
    {
       $db = $this->getSlaveDB();
       $sql = 'SELECT u.uid FROM users u INNER JOIN mytt_network_user nu ON u.uid = nu.uid INNER JOIN mytt_network n ON nu.nwid = n.nwid WHERE n.nwid = ? AND n.status = ' . NETWORK_PUBLISHED;
       return $db->fetchAll($sql, $nid);
    }
    
    public function countMembers($nid)
    {
        $db = $this->getSlaveDB();
        $sql = 'SELECT COUNT(*) FROM users u INNER JOIN mytt_network_user nu ON u.uid = nu.uid INNER JOIN mytt_network n ON nu.nwid = n.nwid WHERE n.nwid = ? AND n.status = ' . NETWORK_PUBLISHED;    
        $count = $db->fetchOne($sql, $nid);
        if ($count) {
            return $count;
        }
        return 0;
    }
    
    public function getAllNetworks()
    {
        $db = $this->getSlaveDB();   
        $result = array();             
        //$sql = 'SELECT * FROM mytt_network n LEFT JOIN mytt_cities c ON n.cityID = c.cid ORDER BY n.cityID, nwid, ntid DESC';
        $sql = "SELECT n.*, c.name as cityName FROM mytt_network n LEFT JOIN mytt_cities c ON n.cityID = c.cid WHERE n.ntid = '1' AND n.cityID = '0'";
        $countries = $db->fetchAll($sql);        
        $result = array_merge($result, $countries);          
        $sql = "SELECT n.*, c.name as cityName FROM mytt_network n INNER JOIN mytt_cities c ON n.cityID = c.cid WHERE n.ntid = '1' AND n.cityID != '0'";
        $cities = $db->fetchAll($sql);
        $sql = "SELECT n.*, c.name as cityName FROM mytt_network n INNER JOIN mytt_cities c ON n.cityID = c.cid WHERE n.ntid != '1' AND n.cityID != '0' ORDER BY ntid DESC";
        $schools = $db->fetchAll($sql);
        foreach ($cities as $city){
            $result[] = $city;
            foreach ($schools as $school){
                if ($school->cityID == $city->cityID){
                    $result[] = $school;
                }
            }
        }     
        return $result;
    }
    
    public function getNetworkByName($name)
    {
        $db = $this->getSlaveDB();    
        $sql = 'SELECT * FROM mytt_network WHERE name = ?';
        return $db->fetchRow($sql, $name);    
    }
}

